﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public sealed class StrategyDataListDAL : IStrategyDataDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public int Insert(StrategyDataInfo strategyDataInfo)
        {
            try
            {
                /*dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@yearstart", strategyDataInfo.YearStart);
                dbManager.AddParameters(1, "@yearend", strategyDataInfo.YearEnd);

                strSQL = "SELECT YEAR_START, YEAR_END, FROM BGM_STRATEGY_DATA_LIST ";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                var x = @yearstart;
                var y = @yearend;
                if (x > y)
                {
                    return false;
                }
                else
                {
                    return true;
                }*/
                

                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@code", strategyDataInfo.Code);
                dbManager.AddParameters(1, "@name", strategyDataInfo.Name);
                dbManager.AddParameters(2, "@descr", strategyDataInfo.Description);
                dbManager.AddParameters(3, "@type", strategyDataInfo.StrategyType.Sequence);
                dbManager.AddParameters(4, "@yearstart", strategyDataInfo.YearStart);
                dbManager.AddParameters(5, "@yearend", strategyDataInfo.YearEnd);
                dbManager.AddParameters(6, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@gfmis_code", strategyDataInfo.Gfmis_code);

                return dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO BGM_STRATEGY_DATA_LIST(BGM_STRAT_LIST_CODE, BGM_STRAT_LIST_NAME"
                        + ", BGM_STRAT_LIST_DESCR, BGM_STRAT_TYPE_SEQ, YEAR_START, YEAR_END, RECORD_STATUS, GFMIS_CODE)"
                    + " VALUES (@code, @name, @descr, @type, @yearstart, @yearend, @status, @gfmis_code)");
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(StrategyDataInfo strategyDataInfo)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@seq", strategyDataInfo.Sequence);
                dbManager.AddParameters(1, "@code", strategyDataInfo.Code);
                dbManager.AddParameters(2, "@name", strategyDataInfo.Name);
                dbManager.AddParameters(3, "@descr", strategyDataInfo.Description);
                dbManager.AddParameters(4, "@type", strategyDataInfo.StrategyType.Sequence);
                dbManager.AddParameters(5, "@yearstart", strategyDataInfo.YearStart);
                dbManager.AddParameters(6, "@yearend", strategyDataInfo.YearEnd);
                dbManager.AddParameters(7, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@gfmis_code", strategyDataInfo.Gfmis_code);

                strSQL = "UPDATE BGM_STRATEGY_DATA_LIST"
                + " SET BGM_STRAT_LIST_CODE = @code"
                    + ", BGM_STRAT_LIST_NAME = @name"
                    + ", BGM_STRAT_LIST_DESCR = @descr"
                    + ", BGM_STRAT_TYPE_SEQ = @type"
                    + ", YEAR_START = @yearstart"
                    + ", YEAR_END = @yearend"
                    + ", RECORD_STATUS = @status"
                    + ", gfmis_code = @gfmis_code"
                + " WHERE BGM_STRAT_LIST_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_STRATEGY_DATA_LIST SET RECORD_STATUS = @status WHERE BGM_STRAT_LIST_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<StrategyDataInfo> Search(StrategyDataInfo strategyDataInfo)
        {
            string strSQL;
            List<StrategyDataInfo> stratList = new List<StrategyDataInfo>();
            int type = 0;

            if(strategyDataInfo.StrategyType != null)
                type = strategyDataInfo.StrategyType.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", strategyDataInfo.Sequence);
                dbManager.AddParameters(1, "@code", strategyDataInfo.Code);
                dbManager.AddParameters(2, "@name", strategyDataInfo.Name);
                dbManager.AddParameters(3, "@descr", strategyDataInfo.Description);
                dbManager.AddParameters(4, "@yearstart", strategyDataInfo.YearStart);
                dbManager.AddParameters(5, "@yearend", strategyDataInfo.YearEnd);
                dbManager.AddParameters(6, "@type", type);
                dbManager.AddParameters(7, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BGM_STRAT_LIST_SEQ, BGM_STRAT_LIST_CODE, BGM_STRAT_LIST_NAME, BGM_STRAT_LIST_DESCR"
                    + ", YEAR_START, YEAR_END, RECORD_STATUS, BGM_STRAT_TYPE_SEQ, GFMIS_CODE"
                    + " FROM BGM_STRATEGY_DATA_LIST"
                    + " WHERE RECORD_STATUS not like @status"
                        + ((strategyDataInfo.Sequence > 0) ? " AND BGM_STRAT_LIST_SEQ = @seq" : "")
                        + ((strategyDataInfo.Code.Length > 0) ? " AND BGM_STRAT_LIST_CODE = @code" : "")
                        + ((strategyDataInfo.Name.Length > 0) ? " AND BGM_STRAT_LIST_NAME = @name" : "")
                        + ((strategyDataInfo.Description.Length > 0) ? " AND BGM_STRAT_LIST_DESCR = @descr" : "")
                        + ((type > 0) ? " AND BGM_STRAT_TYPE_SEQ = @type" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    StrategyDataInfo stratRecord = new StrategyDataInfo();
                    StrategyTypeDAL stratType = new StrategyTypeDAL();
                    IList<StrategyTypeInfo> stratTypeList = stratType.Search(new StrategyTypeInfo(Convert.ToInt32(dr["BGM_STRAT_TYPE_SEQ"]), "", "", "", "", 0, null));

                    stratRecord.Sequence = Convert.ToInt32(dr["BGM_STRAT_LIST_SEQ"]);
                    stratRecord.Code = dr["BGM_STRAT_LIST_CODE"].ToString();
                    stratRecord.Name = dr["BGM_STRAT_LIST_NAME"].ToString();
                    stratRecord.Description = dr["BGM_STRAT_LIST_DESCR"].ToString();
                    stratRecord.YearStart = dr["YEAR_START"].ToString();
                    stratRecord.YearEnd = dr["YEAR_END"].ToString();
                    stratRecord.Status = dr["RECORD_STATUS"].ToString();
                    if (stratTypeList.Count > 0)
                    {
                        stratRecord.StrategyType = stratTypeList[0];
                        stratRecord.Type = stratTypeList[0].Name;//dr["BGM_STRAT_TYPE_SEQ"].ToString();
                    }
                    stratRecord.Gfmis_code = dr["GFMIS_CODE"].ToString();

                    stratList.Add(stratRecord);
                }

                //while (rdr.Read())
                //{
                //    StrategyTypeDAL stratType = new StrategyTypeDAL();

                //    IList<StrategyTypeInfo> stratTypeList = stratType.Search(new StrategyTypeInfo(rdr.GetInt32(7), "", "", "", "", 0, null));

                //    if (stratTypeList != null && stratTypeList.Count > 0)
                //    {
                //        StrategyDataInfo stratRecord = new StrategyDataInfo(rdr.GetInt32(0), rdr.GetString(1)
                //                                                            , rdr.GetString(2)
                //                                                            , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                //                                                            , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                //                                                            , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                //                                                            , rdr.GetString(6)
                //                                                            , stratTypeList[0].Name
                //                                                            , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                //                                                            , stratTypeList[0], null);
                //        stratList.Add(stratRecord);
                //    }
                //}

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return stratList;
        }

        public bool CheckDuplicateCode(string bgm_strat_list_code, string current_bgm_strat_list_seq)
        {
            bool result = false;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@deletestatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_strat_list_code", bgm_strat_list_code);
                dbManager.AddParameters(2, "@bgm_strat_list_seq", current_bgm_strat_list_seq);

                string strSql = @"SELECT bgm_strat_list_code
                            FROM bgm_strategy_data_list
                            WHERE bgm_strat_list_code = @bgm_strat_list_code "
                            + (String.IsNullOrEmpty(current_bgm_strat_list_seq)
                                ? String.Empty 
                                : " AND bgm_strat_list_seq not like @bgm_strat_list_seq " )
                            + "AND record_status not like @deletestatus";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSql);

                while (rdr.Read())
                {
                    result = true; // found duplicate
                    break;
                }
            }
            catch (Exception error)
            {
                
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }
    }
}